Web Development

LDAP save manage users in php mysql yii

Yii

28.Dec.2020
ldap-active-directory-save-manage-users-in-php-mysql-yii

LDAP Save & Manage Users in PHP MySQL Yii

By integrating LDAP (Lightweight Directory Access Protocol) with a PHP MySQL Yii application, developers can build a secure and scalable solution for user authentication and management. LDAP acts as a centralized directory service, making it easier to manage users, while the Yii framework allows for the development of feature-rich web applications with seamless LDAP integration.

In many web applications, a users or employee table is created in the database to store user information. However, in some cases, it is necessary to fetch users from Active Directory via LDAP and store them in a MySQL table, including manager information. This tutorial focuses on Active Directory user management using LDAP in Yii2.

Prerequisites for LDAP Integration

Before implementing LDAP in your Yii2 application, ensure you have:

  • A Yii2 framework setup. You can download it here.
  • Active Directory domain credentials:
    • $domain: Your LDAP or Active Directory domain
    • $ldap_username: LDAP account username
    • $ldap_password: LDAP account password
  • A MySQL users table to store the fetched data, including manager information.

Fetching Users from Active Directory Using PHP Yii

You can use a custom function, such as GetAddataArray, to fetch users. This function requires three parameters:

GetAddataArray($domain, $ldap_username, $ldap_password);

Parameters explained:

  • $domain – The Active Directory domain to fetch records from.
  • $ldap_username – The LDAP username with permissions to access the directory.
  • $ldap_password – The corresponding password for the LDAP user.

The function returns an array with the following structure:

  • TotalCount – The total number of users fetched
  • EmployeeList – An array containing individual employee details

This allows you to store Active Directory users in your MySQL database, including their manager information.

Integrating LDAP Function in Yii2

The GetAddataArray function can be easily integrated into any MVC PHP framework, including Yii2. Steps include:

  1. Create the LDAP connection using your domain credentials.
  2. Fetch all users from Active Directory.
  3. Store users in MySQL along with manager relationships.
  4. Use the data for authentication, reporting, or role management within your Yii2 application.

You can also customize this function to meet specific requirements, such as filtering by department or role.

Benefits of Storing AD Users in MySQL

  • Faster queries for user-related operations
  • Enables custom reporting and dashboards
  • Supports role-based access control in your application
  • Reduces dependency on live LDAP queries for every operation

By combining LDAP with MySQL, you get a balance between real-time directory synchronization and efficient application performance.

									     
public function getAddataArray($domain,$ldap_username,$ldap_password){
      $ldap_columns = NULL;
      $ldap_connection = NULL;
      $output_res=NULL;
      $ldap_username = $ldap_username.'@'.$domain;
      $ldap_connection = @ldap_connect($domain);
      if (FALSE === $ldap_connection){
        $output_res="<h5>Failed to connect</h5>";
      }
      ldap_set_option($ldap_connection, LDAP_OPT_PROTOCOL_VERSION, 3) or die('Unable to set LDAP protocol version');
      ldap_set_option($ldap_connection, LDAP_OPT_REFERRALS, 0); 

      if (TRUE !== ldap_bind($ldap_connection, $ldap_username, $ldap_password)){
        $output_res='<h5>Failed to bind to LDAP server.</h5>';
      }
      $ldap_base_dn = 'DC=domain-dc-name-here,DC=com';
      $search_filter = "(&(objectCategory=user))";
      $result = @ldap_search($ldap_connection, $ldap_base_dn, $search_filter);
      $total_records=0;
      if (FALSE !== $result){
        $entries = @ldap_get_entries($ldap_connection, $result);
        if ($entries['count'] > 0){
          for ($x=0; $x<$entries['count']; $x++){
               if (!empty($entries[$x]['givenname'][0]) &&
               !empty($entries[$x]['mail'][0]) &&
                !empty($entries[$x]['department'][0]) &&
               !empty($entries[$x]['samaccountname'][0]) &&
               !empty($entries[$x]['sn'][0]) &&
               !empty($entries[$x]['physicaldeliveryofficename'][0]) &&
               'Shop' !== $entries[$x]['sn'][0] &&
               'Account' !== $entries[$x]['sn'][0]){
                
                $location=!empty($entries[$x]['physicaldeliveryofficename'][0]) ? $entries[$x]['physicaldeliveryofficename'][0] : NULL;                
                $sAMAccountName=!empty($entries[$x]['samaccountname'][0]) ? strtoupper(trim($entries[$x]['samaccountname'][0])) : NULL;   
                $f_name=isset($entries[$x]['givenname'][0]) ? trim(ucfirst($entries[$x]['givenname'][0])) : NULL;
                $l_name=isset($entries[$x]['sn'][0]) ? trim(ucfirst($entries[$x]['sn'][0])) : NULL;
                $employee_name=ucwords($f_name.' '.$l_name);
                $createdon=$this->ldap_dt($entries[$x]['whencreated'][0], 'Y-m-d H:i:s'); 
                $updatedon=$this->ldap_dt($entries[$x]['whenchanged'][0], 'Y-m-d H:i:s'); 
                $distinguishedname=explode(",",$entries[$x]['distinguishedname'][0]);
                $streetaddress=!empty($entries[$x]['streetaddress'][0]) ? $entries[$x]['streetaddress'][0] : NULL;
                $homephone=!empty($entries[$x]['homephone'][0]) ? $entries[$x]['homephone'][0] : NULL;
                $Designation=!empty($entries[$x]['title'][0]) ? ucwords($entries[$x]['title'][0]) : NULL;
                $company=!empty($entries[$x]['company'][0]) ? ucwords($entries[$x]['company'][0]) : NULL; 
                $pincode=!empty($entries[$x]['postalcode'][0]) ? $entries[$x]['postalcode'][0] :NULL;
                $state=!empty($entries[$x]['st'][0]) ? ucwords($entries[$x]['st'][0]) :NULL;
                $Department=!empty($entries[$x]['department'][0]) ? ucwords(trim($entries[$x]['department'][0])) : NULL;
                $mobile=!empty($entries[$x]['mobile'][0]) ? strtolower(trim($entries[$x]['mobile'][0])) :NULL;
                $emailid=!empty($entries[$x]['mail'][0]) ? strtolower(trim($entries[$x]['mail'][0])) : NULL;
                list($a, $b, $c,$d) =$distinguishedname;
                if (preg_match('/\OU=\b/', $c)) {
                   $Unit=strtoupper(str_replace('OU=','',$c));
                }else{
                   $Unit=strtoupper(str_replace('DC=','',$c));
                }
                $manager =!empty($entries[$x]["manager"][0]) ? str_replace('CN=','',strstr($entries[$x]["manager"][0],',',true)) : NULL;
                $reportingTo=$this->getManagerInfo($ldap_connection,$manager);
                $output_res[$sAMAccountName] = array('EmployeeNo'=>$sAMAccountName,'Department'=>$Department,'Mobile'=>$mobile,'EmailId' => $emailid,'EmployeeName' =>$employee_name,'Location'=>$location,'Unit'=>$Unit,'extension'=>$homephone,'updatedon'=>$updatedon,'createdon'=>$createdon,'Company'=>$company,'Designation'=>$Designation,'PinCode'=>$pincode,'state'=>$state,'reportingTo'=>$reportingTo);
                $total_records++;
            }
          }
          }
      }
      ldap_unbind($ldap_connection); 
      usort($output_res, function ($item1, $item2) {
        return $item1['EmployeeName'] <=> $item2['EmployeeName'];
      });
      return array('TotalCount'=>$total_records,'EmployeeList'=>$output_res);
  }

Inside GetAddataArray function , we are calling another function getManagerInfo , which will return you name,EmployeeNo,email of manager of that user. getManagerInfo function required two parameter one is ldap connection and manager name , we will use ldap_search and pass cn=$manager_name to get manager information.

									     
public function getManagerInfo($ldap_connection,$manager_name){
    $ad = $ldap_connection;
    $filter = "(&(objectClass=user)(cn=$manager_name))";
    $dn = 'DC=domain-dc-name-here,DC=com';
    $result=@ldap_search($ad,$dn,$filter);
    $output_res=NULL;
    if (FALSE !== $result){
      $entries = @ldap_get_entries($ldap_connection, $result);
      if ($entries['count'] > 0){
        $counter = 0;
        foreach ($entries AS $key => $ldap_value) {
           $f_name=!empty($entries[$key]['givenname'][0]) ? $entries[$key]['givenname'][0] : NULL;
           $l_name=!empty($entries[$key]['sn'][0]) ? $entries[$key]['sn'][0] : NULL;
           $employee_name=$f_name.' '.$l_name;
           $sAMAccountName=$employee_name;
           $output_res = array('name'=>$sAMAccountName,'EmployeeNo'=>$entries[$key]['samaccountname'][0],'email'=>$entries[$key]['mail'][0]);
        }
      }
    }
    return $output_res;
  }

In yii2 , you can call actionIndex function and execute it. 

									     
public function actionIndex(){
	$domain 	= "yourdomain.com";
	$ldapuser 	= "ldapuser";
	$password	= 'password';
	$dataProvider=$this->getAddataArray($domain,$ldapuser,$password);
	if(!empty($dataProvider)){
	  $this->SaveAdDataMannual($dataProvider);
	}
	return ExitCode::OK;
}

SaveAdDataMannual function will save new records and will update other wise. it will return $newcount=0 ,$savecount=0 for let us know that how many records will be newly created or updated. you can easily create a table which will update save data comming from active directory. you can also set a cron job for automated process so that mysql table or whatever table you made will update or save records.

									     
public function SaveAdDataMannual_old($dataProvider){
		$resultAD = $dataProvider['EmployeeList'];;
		$model = new PortalLiveAdMaster();
		$updateinfo = PortalLiveAdMaster::find()->asArray()->all();
		$newcount=0;
		$savecount=0;
		for($i=0;$i<count($resultAD);$i++){
			$model->setIsNewRecord(true);
			$employe_no_up=!empty($updateinfo[$i]['EmployeeNo']) ? $updateinfo[$i]['EmployeeNo'] : NULL;
			if($employe_no_up == trim($resultAD[$i]['EmployeeNo'])){
					$model=PortalLiveAdMaster::findOne($updateinfo[$i]['id']);
					$model->EmployeeNo= trim($resultAD[$i]['EmployeeNo']);
					$model->Department=trim($resultAD[$i]['Department']);
					$model->Mobile=trim($resultAD[$i]['Mobile']);
					$model->EmailId=trim($resultAD[$i]['EmailId']);
					$model->EmployeeName=trim($resultAD[$i]['EmployeeName']);
					$model->Location=trim($resultAD[$i]['Location']);
					$model->Unit=trim($resultAD[$i]['Unit']);
					$model->extension=trim($resultAD[$i]['extension']);
					$model->updatedon=trim($resultAD[$i]['updatedon']);
					$model->createdon=trim($resultAD[$i]['createdon']);
					$model->Company=trim($resultAD[$i]['Company']);
					$model->Designation=trim($resultAD[$i]['Designation']);
					$model->PinCode=trim($resultAD[$i]['PinCode']);
					$model->state=trim($resultAD[$i]['state']);
					$model->reportingTo=trim($resultAD[$i]['reportingTo']['EmployeeNo']);
					$model->reportingToEmail=trim($resultAD[$i]['reportingTo']['email']);
					$model->reportingToName=trim($resultAD[$i]['reportingTo']['name']);
					$model->save($updateinfo[$i]['id']);	
					$savecount++;
		   }else{
				    $model->id = null;
					$model->EmployeeNo= trim($resultAD[$i]['EmployeeNo']);
					$model->Department=trim($resultAD[$i]['Department']);
					$model->Mobile=trim($resultAD[$i]['Mobile']);
					$model->EmailId=trim($resultAD[$i]['EmailId']);
					$model->EmployeeName=trim($resultAD[$i]['EmployeeName']);
					$model->Location=trim($resultAD[$i]['Location']);
					$model->Unit=trim($resultAD[$i]['Unit']);
					$model->extension=trim($resultAD[$i]['extension']);
					$model->updatedon=trim($resultAD[$i]['updatedon']);
					$model->createdon=trim($resultAD[$i]['createdon']);
					$model->Company=trim($resultAD[$i]['Company']);
					$model->Designation=trim($resultAD[$i]['Designation']);
					$model->PinCode=trim($resultAD[$i]['PinCode']);
					$model->state=trim($resultAD[$i]['state']);
					$model->reportingTo=trim($resultAD[$i]['reportingTo']['EmployeeNo']);
				    $model->reportingToEmail=trim($resultAD[$i]['reportingTo']['email']);
					$model->reportingToName=trim($resultAD[$i]['reportingTo']['name']);
					$model->save();	
					$newcount++;
		   }	
		}
	}

PortalLiveAdMaster will be your yii2 model class which will holds the database schema or defination of table to perform crud operartions.

									     
class PortalLiveAdMaster extends \yii\db\ActiveRecord
{
    /**
     * {@inheritdoc}
     */
    public static function tableName()
    {
        return 'portal_live_ad_master';
    }

    /**
     * {@inheritdoc}
     */
    public function rules()
    {
        return [
            [['EmployeeNo', 'EmployeeName'], 'required'],
            [['extension', 'PinCode'], 'integer'],
            [['updatedon', 'createdon'], 'safe'],
            [['EmployeeNo', 'Department', 'Unit', 'Company', 'Designation', 'state', 'reportingTo', 'reportingToName'], 'string', 'max' => 50],
            [['Mobile'], 'string', 'max' => 20],
            [['EmailId', 'reportingToEmail'], 'string', 'max' => 255],
            [['EmployeeName', 'Location'], 'string', 'max' => 100],
        ];
    }

    /**
     * {@inheritdoc}
     */
    public function attributeLabels()
    {
        return [
            'id' => 'ID',
            'EmployeeNo' => 'Employee No',
            'Department' => 'Department',
            'Mobile' => 'Mobile',
            'EmailId' => 'Email ID',
            'EmployeeName' => 'Employee Name',
            'Location' => 'Location',
            'Unit' => 'Unit',
            'extension' => 'Extension',
            'updatedon' => 'Updatedon',
            'createdon' => 'Createdon',
            'Company' => 'Company',
            'Designation' => 'Designation',
            'PinCode' => 'Pin Code',
            'state' => 'State',
            'reportingTo' => 'Reporting To',
            'reportingToEmail' => 'Reporting To Email',
            'reportingToName' => 'Reporting To Name',
        ];
    }
}

Conclusion

Integrating LDAP with a PHP MySQL Yii application is an efficient way to manage users and their managers. With the GetAddataArray function and Yii2 framework, developers can sync Active Directory users, maintain manager relationships, and improve overall user management workflows. This approach ensures a secure, scalable, and centralized solution, making your application ready for enterprise-level deployments.